# added by yinchao, Fri July 05 11:09	2024
# many to many relationship in database.

# create tables;

import sqlite3

with sqlite3.connect('many2many.db3') as conn:
    cursor = conn.cursor()

    # 创建表
    cursor.execute('DROP TABLE IF EXISTS Course')
    cursor.execute('DROP TABLE IF EXISTS User')
    cursor.execute('DROP TABLE IF EXISTS Member')

    # UNIQUE 是约束, 也是索引(提高搜索速度)
    cursor.execute('''CREATE TABLE Course (
        id INTEGER PRIMARY KEY,
        title TEXT UNIQUE
    )''')

    cursor.execute('''CREATE TABLE User (
        id INTEGER PRIMARY KEY,
        name TEXT UNIQUE
    )''')

    cursor.execute('''CREATE TABLE Member (
        user_id INTEGER,
        course_id INTEGER,
        PRIMARY KEY (user_id, course_id)
    )''')

    conn.commit()

# primary key 可以设置在两个 columns 之上 ???
